
import pandas as pd
import numpy as np
import plotly.express as px
from matplotlib import pyplot as plt
import seaborn as sns
from plotly.offline import plot
from plotly.subplots import make_subplots
import plotly.subplots as sp


#Normalising and weighting factors from PEF
xls = pd.ExcelFile('NF_WF.xlsx')
nf = pd.read_excel(xls,0)
nf
nf.dtypes

Impact category     object
NF                 float64
WF                 float64
dtype: object

nf


#Overall results from brightway
xls1 = pd.ExcelFile('Overall results.xlsx')
OI = pd.read_excel(xls1,0)
OI


df3 = OI.merge(nf, on="Impact category", how='outer')
df3


#Create dataframes for normalised and weighted results

normalised_results = df3[['S1', 'S1_no K', 'S2', 'S3', 'S4']].divide(nf["NF"], axis="index")
weighted_results = normalised_results[['S1', 'S1_no K', 'S2', 'S3', 'S4']].multiply(nf["WF"], axis="index")


extracted_col = OI["Impact category"]
 
weighted_results = weighted_results.join(extracted_col)
cols = weighted_results.columns.tolist()
cols = cols[-1:] + cols[:-1]

weighted_results = weighted_results[cols]
weighted_results

list=['Acidification', 'Human toxicity, cancer', 'Climate change', 'Ecotoxicity, freshwater', 'Eutrophication, freshwater',
     'Eutrophication, marine', 'Eutrophication, terrestrial', 'Ionising radiation', 'Land use', 'Human toxicity, non-cancer',
     'Ozone depletion', 'Photochemical ozone formation', 'Resource use, fossils', 'Resource use, minerals and metals', 'Particulate matter',
     'Water use']

weighted_results['IC'] = list


weighted_results


df_final= weighted_results.drop(['Impact category'], axis=1)
df_final.to_csv("test.csv")


#Melting dataframe of weighted results to prepare a stacked bar chart using plotly

df_melted = df_final.reset_index().melt(id_vars='IC')
df_melted
df_melted_final = df_melted.drop([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15])


df_melted_final.head()


#Setting customised template using plotly

import plotly.io as pio
import plotly.graph_objects as go
pio.templates["myname"] = go.layout.Template(
    layout=go.Layout(
        colorway=['#003366', '#E31B23', '#D81A7E', '#2790CC', '#252829', '#CC6B31', '#9AC587', 
                  '#0F62EC', '#D227F5', '#4DFF97', '#419fde', '#5C3963', '#92B7F5', '#9BBBA8', '#E9EADF', '#1D8C26']
    )
)
pio.templates.default = 'myname'


#Creating stacked bar chart

fig = px.bar(df_melted_final, x="variable", y="value", color="IC", barmode = 'relative', template="myname")

fig.update_layout(
    title={'text':"Weighted impact results"},
    xaxis_title="",
    yaxis_title="points",
    legend_title="",
    font=dict(
        family="Arial",
        size=15,
        color="Black"
    )
)

fig.add_trace(
    go.Scatter(
        x=['S1', 'S1_no K', 'S2', 'S3', 'S4'],
        y=[-0.00361, 0.00206, 0.0010, -0.00585, -0.00445 ], name='Net impact', mode='markers', marker=dict(color='Black', size=12)
    ))

fig.update_layout(
    autosize=False,
    width=800,
    height=900,
    margin=dict(
        l=50,
        r=50,
        b=100,
        t=100,
        pad=4
    ),
    
)

fig.update_yaxes(automargin=True)

#fig.update_yaxes(range=[-0.002, 0.001])

fig.show()
fig.write_image("fig1.pdf")


#Change to absolute values

df_final.update(df_final.select_dtypes(include=[np.number]).abs())


col_list = ["S1", "S2", "S3", "S4"]

totals = df_final.groupby("IC")[col_list].sum()

percentages = totals/df_final[col_list].sum()*100
percentages.nlargest(16, 'S2')


#Read excel file_Monte carlo results for most impacting categories
xls = pd.ExcelFile('MC_results.xlsx')
df1= pd.read_excel(xls, 0) #Acidification
df2 = pd.read_excel(xls,1) #Climate change
df3 = pd.read_excel(xls,2) #Freshwater Ecotoxicity
df4 = pd.read_excel(xls,3) #Terrestrial Eutrophication
df5 = pd.read_excel(xls,4) #Material resources: metals/minerals
df6 = pd.read_excel(xls,5) #Particulate matter formation

df_Acidification = pd.melt(df1, id_vars =[], value_vars=['S1', 'S1_no K', 'S2', 'S3', 'S4'])
df_CC = pd.melt(df2, id_vars=[], value_vars=['S1', 'S1_no K', 'S2', 'S3', 'S4'])
df_FE = pd.melt(df3, id_vars=[], value_vars=['S1', 'S1_no K', 'S2', 'S3', 'S4']) 
df_TE = pd.melt(df4, id_vars=[], value_vars=['S1', 'S1_no K', 'S2', 'S3', 'S4'])


df1.describe()


df1.median()

S1         0.187842
S1_no K    0.573040
S2         0.873551
S3         0.091074
S4         0.120511
dtype: float64


df3.describe()


import plotly.express as px


figure1 = px.violin(df_CC, y="value", color="variable", box=True, # draw box plot inside the violin
                points='all', # can be 'outliers', or False
               )
figure2 = px.violin(df_FE, y="value", color="variable", box=True, # draw box plot inside the violin
                points='all', # can be 'outliers', or False
               )
figure3 = px.violin(df_Acidification, y="value", color="variable", box=True, # draw box plot inside the violin
                points='all', # can be 'outliers', or False
               )
figure4 = px.violin(df_TE, y="value", color="variable", box=True, # draw box plot inside the violin
                points='all', # can be 'outliers', or False
               )

figure1.show()


impact_categories = ['Climate change potential', 'Freshwater ecotoxicity potential']

fig = go.Figure()
fig = make_subplots(rows=1, cols=2, subplot_titles=impact_categories)
scenarios = ['S1', 'S1_no K', 'S2', 'S3', 'S4']


for variable in scenarios:
    fig.add_trace(go.Violin(x=df_CC['variable'][df_CC['variable'] == variable],
                            y=df_CC['value'][df_CC['variable'] == variable],
                            name=variable, line_color='black',
                            meanline_visible=True, fillcolor='lightseagreen', box_visible=True,
                            ), row=1, col=1)
    
for variable in scenarios:    
     fig.add_trace(go.Violin(x=df_FE['variable'][df_FE['variable'] == variable],
                            y=df_FE['value'][df_FE['variable'] == variable],
                            name=variable, fillcolor='lightseagreen',
                            box_visible=True, line_color='black',
                            meanline_visible=True), row=1, col=2)
        
fig.update_yaxes(title_text="kg CO2-eq", row=1, col=1)
fig.update_yaxes(title_text="CTU-eq", row=1, col=2)        
        
#fig.update_layout(margin=dict(l=20, r=20, t=20, b=20), showlegend=False)
        
fig.update_layout(height=600, width=1000, title_text="", showlegend=False, font_family="Arial",
    font_color="black")        
        
fig.show()


impact_categories = ['Acidification potential','Terrestrial eutrophication potential']

fig = go.Figure()
fig = make_subplots(rows=1, cols=2, subplot_titles=impact_categories)
scenarios = ['S1', 'S1_no K', 'S2', 'S3', 'S4']



for variable in scenarios:    
     fig.add_trace(go.Violin(x=df_Acidification['variable'][df_Acidification['variable'] == variable],
                            y=df_Acidification['value'][df_Acidification['variable'] == variable],
                            name=variable, fillcolor='lightseagreen',
                            box_visible=True, line_color='black',
                            meanline_visible=True), row=1, col=1)
for variable in scenarios:    
     fig.add_trace(go.Violin(x=df_TE['variable'][df_TE['variable'] == variable],
                            y=df_TE['value'][df_TE['variable'] == variable],
                            name=variable, fillcolor='lightseagreen',
                            box_visible=True,line_color='black',
                            meanline_visible=True), row=1, col=2)
                
        
fig.update_yaxes(title_text="mol H+ -eq", row=1, col=1)  
fig.update_yaxes(title_text="mol N-eq", row=1, col=2)  
        
#fig.update_layout(margin=dict(l=20, r=20, t=20, b=20), showlegend=False)
        
fig.update_layout(height=600, width=1000, title_text="", showlegend=False, font_family="Arial",
    font_color="black")        
        
fig.show()


impact_categories = ['Climate change potential', 'Freshwater ecotoxicity potential', 'Acidification potential','Terrestrial eutrophication potential']

fig = go.Figure()
fig = make_subplots(rows=2, cols=2, subplot_titles=impact_categories)
scenarios = ['S1', 'S1_no K', 'S2', 'S3', 'S4']

for variable in scenarios:
    fig.add_trace(go.Violin(x=df_CC['variable'][df_CC['variable'] == variable],
                            y=df_CC['value'][df_CC['variable'] == variable],
                            name=variable, line_color='black',
                            meanline_visible=True, fillcolor='lightseagreen', box_visible=True,
                            ), row=1, col=1)
    
for variable in scenarios:    
     fig.add_trace(go.Violin(x=df_FE['variable'][df_FE['variable'] == variable],
                            y=df_FE['value'][df_FE['variable'] == variable],
                            name=variable, fillcolor='lightseagreen',
                            box_visible=True, line_color='black',
                            meanline_visible=True), row=1, col=2)
        
for variable in scenarios:    
     fig.add_trace(go.Violin(x=df_Acidification['variable'][df_Acidification['variable'] == variable],
                            y=df_Acidification['value'][df_Acidification['variable'] == variable],
                            name=variable, fillcolor='lightseagreen',
                            box_visible=True, line_color='black',
                            meanline_visible=True), row=2, col=1)
for variable in scenarios:    
     fig.add_trace(go.Violin(x=df_TE['variable'][df_TE['variable'] == variable],
                            y=df_TE['value'][df_TE['variable'] == variable],
                            name=variable, fillcolor='lightseagreen',
                            box_visible=True,line_color='black',
                            meanline_visible=True), row=2, col=2)
        
fig.update_yaxes(title_text="kg CO2-eq", row=1, col=1)
fig.update_yaxes(title_text="CTU-eq", row=1, col=2)
fig.update_yaxes(title_text="mol H+ -eq", row=2, col=1)  
fig.update_yaxes(title_text="mol N-eq", row=2, col=2)  
        
fig.update_layout(height=1000, width=1000, title_text="", showlegend=False)  
fig.write_image("violins_chap4.pdf")

fig.show()

	Impact category	NF	WF
0	('EF v3.0', 'acidification', 'accumulated exce...	55.569541	0.0620
1	('EF v3.0', 'human toxicity: carcinogenic', 'c...	0.000017	0.0213
2	('EF v3.0', 'climate change', 'global warming ...	8095.525064	0.2106
3	('EF v3.0', 'ecotoxicity: freshwater', 'compar...	42683.161866	0.0192
4	('EF v3.0', 'eutrophication: freshwater', 'fra...	1.606852	0.0280
5	('EF v3.0', 'eutrophication: marine', 'fractio...	19.545182	0.0296
6	('EF v3.0', 'eutrophication: terrestrial', 'ac...	176.755000	0.0371
7	('EF v3.0', 'ionising radiation: human health'...	4220.159813	0.0501
8	('EF v3.0', 'land use', 'soil quality index')	819498.182923	0.0794
9	('EF v3.0', 'human toxicity: non-carcinogenic'...	0.000230	0.0184
10	('EF v3.0', 'ozone depletion', 'ozone depletio...	0.053648	0.0631
11	('EF v3.0', 'photochemical ozone formation: hu...	40.601397	0.0478
12	('EF v3.0', 'energy resources: non-renewable',...	65004.259664	0.0478
13	('EF v3.0', 'material resources: metals/minera...	0.063640	0.0755
14	('EF v3.0', 'particulate matter formation', 'i...	0.000595	0.0896
15	('EF v3.0', 'water use', 'user deprivation pot...	11468.708641	0.0851

	Impact category	S1	S1_no K	S2	S3	S4
0	('EF v3.0', 'acidification', 'accumulated exce...	4.191236e-02	1.313733e-01	1.945575e-01	5.010175e-02	7.172046e-02
1	('EF v3.0', 'human toxicity: carcinogenic', 'c...	1.495376e-09	1.215131e-08	5.948477e-09	2.610967e-08	4.344242e-08
2	('EF v3.0', 'climate change', 'global warming ...	2.555867e+01	4.043902e+01	3.199219e+01	1.036470e+01	2.031627e+01
3	('EF v3.0', 'ecotoxicity: freshwater', 'compar...	-7.942218e+03	-5.943950e+01	-5.358028e+02	-8.617799e+03	-8.948824e+03
4	('EF v3.0', 'eutrophication: freshwater', 'fra...	-2.595619e-03	4.679963e-04	-4.991381e-04	1.791030e-03	5.351790e-03
5	('EF v3.0', 'eutrophication: marine', 'fractio...	2.367009e-02	4.207963e-02	8.412027e-02	1.425948e-01	6.604656e-02
6	('EF v3.0', 'eutrophication: terrestrial', 'ac...	2.293574e-01	5.566238e-01	8.906047e-01	1.971668e-01	1.766721e-01
7	('EF v3.0', 'ionising radiation: human health'...	-7.701690e-01	-1.216665e-01	-1.537047e-01	-1.024843e+00	-9.053776e-01
8	('EF v3.0', 'land use', 'soil quality index')	-8.903019e+01	6.793677e+01	2.369642e+01	-1.504055e+02	-1.663408e+01
9	('EF v3.0', 'human toxicity: non-carcinogenic'...	-3.287681e-07	1.681226e-07	6.775181e-09	-4.029592e-07	-1.144994e-07
10	('EF v3.0', 'ozone depletion', 'ozone depletio...	9.802617e-07	2.931790e-06	1.194994e-06	2.195652e-05	7.732357e-07
11	('EF v3.0', 'photochemical ozone formation: hu...	1.749958e-02	5.390064e-02	2.627887e-02	1.273081e-02	4.642134e-02
12	('EF v3.0', 'energy resources: non-renewable',...	5.589415e+01	2.662736e+02	7.659389e+01	-1.469276e+02	6.024228e+01
13	('EF v3.0', 'material resources: metals/minera...	-6.614389e-04	1.644920e-04	-2.611408e-04	-2.019176e-03	-1.177287e-03
14	('EF v3.0', 'particulate matter formation', 'i...	2.217632e-07	1.340719e-06	1.626979e-06	1.415006e-07	6.990096e-07
15	('EF v3.0', 'water use', 'user deprivation pot...	-8.137132e+00	3.122987e-01	-6.719087e+00	-1.679302e+01	-1.442245e+01

	Impact category	S1	S1_no K	S2	S3	S4	NF	WF
0	('EF v3.0', 'acidification', 'accumulated exce...	4.191236e-02	1.313733e-01	1.945575e-01	5.010175e-02	7.172046e-02	55.569541	0.0620
1	('EF v3.0', 'human toxicity: carcinogenic', 'c...	1.495376e-09	1.215131e-08	5.948477e-09	2.610967e-08	4.344242e-08	0.000017	0.0213
2	('EF v3.0', 'climate change', 'global warming ...	2.555867e+01	4.043902e+01	3.199219e+01	1.036470e+01	2.031627e+01	8095.525064	0.2106
3	('EF v3.0', 'ecotoxicity: freshwater', 'compar...	-7.942218e+03	-5.943950e+01	-5.358028e+02	-8.617799e+03	-8.948824e+03	42683.161866	0.0192
4	('EF v3.0', 'eutrophication: freshwater', 'fra...	-2.595619e-03	4.679963e-04	-4.991381e-04	1.791030e-03	5.351790e-03	1.606852	0.0280
5	('EF v3.0', 'eutrophication: marine', 'fractio...	2.367009e-02	4.207963e-02	8.412027e-02	1.425948e-01	6.604656e-02	19.545182	0.0296
6	('EF v3.0', 'eutrophication: terrestrial', 'ac...	2.293574e-01	5.566238e-01	8.906047e-01	1.971668e-01	1.766721e-01	176.755000	0.0371
7	('EF v3.0', 'ionising radiation: human health'...	-7.701690e-01	-1.216665e-01	-1.537047e-01	-1.024843e+00	-9.053776e-01	4220.159813	0.0501
8	('EF v3.0', 'land use', 'soil quality index')	-8.903019e+01	6.793677e+01	2.369642e+01	-1.504055e+02	-1.663408e+01	819498.182923	0.0794
9	('EF v3.0', 'human toxicity: non-carcinogenic'...	-3.287681e-07	1.681226e-07	6.775181e-09	-4.029592e-07	-1.144994e-07	0.000230	0.0184
10	('EF v3.0', 'ozone depletion', 'ozone depletio...	9.802617e-07	2.931790e-06	1.194994e-06	2.195652e-05	7.732357e-07	0.053648	0.0631
11	('EF v3.0', 'photochemical ozone formation: hu...	1.749958e-02	5.390064e-02	2.627887e-02	1.273081e-02	4.642134e-02	40.601397	0.0478
12	('EF v3.0', 'energy resources: non-renewable',...	5.589415e+01	2.662736e+02	7.659389e+01	-1.469276e+02	6.024228e+01	65004.259664	0.0478
13	('EF v3.0', 'material resources: metals/minera...	-6.614389e-04	1.644920e-04	-2.611408e-04	-2.019176e-03	-1.177287e-03	0.063640	0.0755
14	('EF v3.0', 'particulate matter formation', 'i...	2.217632e-07	1.340719e-06	1.626979e-06	1.415006e-07	6.990096e-07	0.000595	0.0896
15	('EF v3.0', 'water use', 'user deprivation pot...	-8.137132e+00	3.122987e-01	-6.719087e+00	-1.679302e+01	-1.442245e+01	11468.708641	0.0851

	Impact category	S1	S1_no K	S2	S3	S4	IC
0	('EF v3.0', 'acidification', 'accumulated exce...	0.000047	0.000147	2.170716e-04	0.000056	8.001989e-05	Acidification
1	('EF v3.0', 'human toxicity: carcinogenic', 'c...	0.000002	0.000015	7.497412e-06	0.000033	5.475446e-05	Human toxicity, cancer
2	('EF v3.0', 'climate change', 'global warming ...	0.000665	0.001052	8.322567e-04	0.000270	5.285151e-04	Climate change
3	('EF v3.0', 'ecotoxicity: freshwater', 'compar...	-0.003573	-0.000027	-2.410181e-04	-0.003877	-4.025414e-03	Ecotoxicity, freshwater
4	('EF v3.0', 'eutrophication: freshwater', 'fra...	-0.000045	0.000008	-8.697669e-06	0.000031	9.325695e-05	Eutrophication, freshwater
5	('EF v3.0', 'eutrophication: marine', 'fractio...	0.000036	0.000064	1.273951e-04	0.000216	1.000235e-04	Eutrophication, marine
6	('EF v3.0', 'eutrophication: terrestrial', 'ac...	0.000048	0.000117	1.869335e-04	0.000041	3.708261e-05	Eutrophication, terrestrial
7	('EF v3.0', 'ionising radiation: human health'...	-0.000009	-0.000001	-1.824719e-06	-0.000012	-1.074827e-05	Ionising radiation
8	('EF v3.0', 'land use', 'soil quality index')	-0.000009	0.000007	2.295912e-06	-0.000015	-1.611652e-06	Land use
9	('EF v3.0', 'human toxicity: non-carcinogenic'...	-0.000026	0.000013	5.428188e-07	-0.000032	-9.173546e-06	Human toxicity, non-cancer
10	('EF v3.0', 'ozone depletion', 'ozone depletio...	0.000001	0.000003	1.405535e-06	0.000026	9.094687e-07	Ozone depletion
11	('EF v3.0', 'photochemical ozone formation: hu...	0.000021	0.000063	3.093810e-05	0.000015	5.465181e-05	Photochemical ozone formation
12	('EF v3.0', 'energy resources: non-renewable',...	0.000041	0.000196	5.632227e-05	-0.000108	4.429834e-05	Resource use, fossils
13	('EF v3.0', 'material resources: metals/minera...	-0.000785	0.000195	-3.098058e-04	-0.002395	-1.396681e-03	Resource use, minerals and metals
14	('EF v3.0', 'particulate matter formation', 'i...	0.000033	0.000202	2.448446e-04	0.000021	1.051942e-04	Particulate matter
15	('EF v3.0', 'water use', 'user deprivation pot...	-0.000060	0.000002	-4.985690e-05	-0.000125	-1.070173e-04	Water use

	S1	S2	S3	S4
IC
Climate change	12.311017	35.893143	3.707425	7.948369
Resource use, minerals and metals	14.529375	13.361148	32.937552	21.004771
Particulate matter	0.617932	10.559533	0.292799	1.582022
Ecotoxicity, freshwater	66.149845	10.394505	53.301962	60.538438
Acidification	0.865843	9.361751	0.768617	1.203424
Eutrophication, terrestrial	0.891368	8.061973	0.569034	0.557687
Eutrophication, marine	0.663734	5.494230	2.969327	1.504260
Resource use, fossils	0.761018	2.429038	1.485565	0.666205
Water use	1.117966	2.150203	1.713350	1.609440
Photochemical ozone formation	0.381467	1.334282	0.206084	0.821912
Eutrophication, freshwater	0.837463	0.375109	0.429128	1.402497
Human toxicity, cancer	0.034898	0.323345	0.452490	0.823455
Land use	0.159717	0.099017	0.200373	0.024238
Ionising radiation	0.169292	0.078696	0.167289	0.161644
Ozone depletion	0.021348	0.060617	0.355093	0.013678
Human toxicity, non-cancer	0.487715	0.023410	0.443912	0.137961

	S1	S1_no K	S2	S3	S4
count	1000.000000	1000.000000	1000.000000	1000.000000	1000.000000
mean	0.147627	0.571694	0.865598	0.067376	0.077656
std	0.254256	0.161770	0.196309	0.872780	0.271397
min	-1.159922	-4.416328	0.289353	-1.475099	-1.480473
25%	0.045733	0.555411	0.814295	-0.094841	-0.041469
50%	0.187842	0.573040	0.873551	0.091074	0.120511
75%	0.277099	0.594870	0.920982	0.230742	0.241919
max	5.142928	0.820206	6.359152	26.155709	3.327081

	S1	S1_no K	S2	S3	S4
count	1000.000000	1000.000000	1000.000000	1000.000000	1000.000000
mean	-9541.643912	-35.835467	-733.101902	-10726.447793	-10793.714803
std	4009.210089	358.967903	543.963445	4915.501054	4697.683224
min	-36619.488061	-1543.878677	-11434.530332	-54499.879162	-44328.047926
25%	-11433.475286	-136.615443	-905.567404	-12906.920705	-13058.946637
50%	-8820.339278	-21.225373	-662.053267	-9888.742888	-9961.677719
75%	-6722.847522	74.701227	-494.537227	-7399.035430	-7456.859401
max	-1665.822979	9204.178491	3637.800089	1861.939254	-1661.562854